package hive.jdbc.getEmpInfo.empDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:lixinlei
 * @Date:2022-04-15 9:34
 **/
public class EmpHiveDao {

    public List<Map<String,Object>> queryEmpsInfo(){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        //初始化员工数据的列表
        List<Map<String,Object>> empsList = new ArrayList<Map<String, Object>>();

        try {
            //加载hive驱动
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            //获取hive连接
            conn = DriverManager.getConnection(
                    "jdbc:hive2://192.168.162.180:10000/default",
                    "bduser",
                    "rootroot");
            //预处理sql语句
            ps = conn.prepareStatement("select empno,ename,sal,dname,loc from emps as e " +
                    " left join dept as d " +
                    " on e.deptno=d.deptno " +
                    " where e.deptno in (?,?,?)");

            ps.setString(1,"10");
            ps.setString(2,"20");
            ps.setString(3,"30");

            //查询结果放在结果集中
            rs = ps.executeQuery();
            //获取数据的字段信息
            ResultSetMetaData metaData = rs.getMetaData();
            //获取字段的数量
            int columnCount = metaData.getColumnCount();
            while(rs.next()){
                //每一条数据封装成一个Map
                Map<String,Object> emp = new HashMap<String, Object>();
                //遍历一行数据的所有字段，封装一个Map
                for (int i = 1; i <= columnCount; i++) {
                    String key = metaData.getColumnName(i);
                    Object value = rs.getObject(key);
                    emp.put(key,value);
                }
                //把一条数据添加到列表中
                empsList.add(emp);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if(null!=rs){
                    rs.close();
                }
                if(null!=ps){
                    ps.close();
                }
                if(null!=conn){
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return empsList;
    }

}
